#include "data_driver.h"
#include <QtSql>

void DataDriver::connect()
{
    QSqlDatabase db = QSqlDatabase::addDatabase("QMYSQL");  //连接的MYSQL的数据库驱动
    db.setHostName("localhost");         //主机名
    db.setPort(3306);                    //端口
    db.setDatabaseName("test_base");          //数据库名
    db.setUserName("root");              //用户名
    db.setPassword("111");            //密码
    db.open();
    qDebug()<< "connected";
}

void DataDriver::insert(const QString& id, const QString& name, const QString& salary)
{
    QSqlQuery query;
    query.prepare("INSERT INTO test_base.student (id, name, salary) "
                  "VALUES (?, ?, ?)");
    query.addBindValue(id.toInt());
    query.addBindValue(name);
    query.addBindValue(salary.toFloat());
    query.exec();
    qDebug() << "insert";
}

void DataDriver::remove(const QString& id)
{
    QSqlQuery query;
    query.prepare("DELETE FROM test_base.student where id = ?");
    query.addBindValue(id.toInt());
    query.exec();
    qDebug() << "deleted";
}

void DataDriver::edit(const QString& id, const QString& salary)
{
    QSqlQuery query;
    query.prepare("UPDATE test_base.student SET salary=? where id=?");
    query.addBindValue(salary.toFloat());
    query.addBindValue(id.toInt());
    query.exec();
    qDebug() << "edited";
}

bool DataDriver::select(const QString& id, TableModel* table)
{
    qDebug() << "ID: "<<id;
    if(id.size() == 0)
    {
        qDebug() << "id size is zero";
        QSqlQuery query("SELECT * FROM test_base.student");
        if(query.next())
        {
            table->removeRows(0, table->rowCount(QModelIndex()), QModelIndex());
            int idNo = query.record().indexOf("id");
            int nameNo = query.record().indexOf("name");
            int salaryNo = query.record().indexOf("salary");
            do
            {
                QString rid = query.value(idNo).toString();
                QString name = query.value(nameNo).toString();
                QString salary = query.value(salaryNo).toString();

                table->insertRows(0, 1, QModelIndex());
                QModelIndex index = table->index(0, 0, QModelIndex());
                table->setData(index, rid, Qt::EditRole);
                index = table->index(0, 1, QModelIndex());
                table->setData(index, name, Qt::EditRole);
                index = table->index(0, 2, QModelIndex());
                table->setData(index, salary, Qt::EditRole);

                qDebug() << rid << name  << salary;
            }while(query.next());

        }
        return true;
    }
    else
    {
        qDebug() << "id size is not zero";
        QSqlQuery query("SELECT name, salary FROM test_base.student where id = " + id);
        if(query.next())
        {
            table->removeRows(0, table->rowCount(QModelIndex()), QModelIndex());
            int nameNo = query.record().indexOf("name");
            int salaryNo = query.record().indexOf("salary");
            do
            {
                QString name = query.value(nameNo).toString();
                QString salary = query.value(salaryNo).toString();

                table->insertRows(0, 1, QModelIndex());
                QModelIndex index = table->index(0, 0, QModelIndex());
                table->setData(index, id, Qt::EditRole);
                index = table->index(0, 1, QModelIndex());
                table->setData(index, name, Qt::EditRole);
                index = table->index(0, 2, QModelIndex());
                table->setData(index, salary, Qt::EditRole);

                qDebug() << name  << salary;
            }while(query.next());

            return true;
        }
        else
        {
            return false;
        }
    }
}

void DataDriver::close()
{
    QSqlDatabase db = QSqlDatabase::database();
    db.close();
}
